package com.greateamd.timetableasy.server.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.greateamd.timetableasy.domain.Cursus;
import com.greateamd.timetableasy.domain.Event;
import com.greateamd.timetableasy.domain.StudyPeriod;
import com.greateamd.timetableasy.server.ServerUtil;
import com.greateamd.timetableasy.server.dao.CursusDAO;
import com.greateamd.timetableasy.server.dao.util.AbstractHibernateDAO;

@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED, readOnly = false)	
@Repository("cursusDAO")
public class CursusDAOImpl extends AbstractHibernateDAO<Cursus,Integer> implements CursusDAO {

	public CursusDAOImpl() {
		super(new Cursus());
	}

	@Override
	public List<Event> getStudyPeriodPlanning(final StudyPeriod studyPeriod,final Date fromDate, final Date toDate) {
		logger.debug("Get "+studyPeriod.getId()+"'s studyPeriod planning from db");
		HibernateCallback callback = new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException,SQLException {
				final List<Event> events = new ArrayList<Event>();
				
				//get sp events
				String hql = "SELECT distinct(event) " +
				 "FROM StudyPeriod sp " +
				 "JOIN sp.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE sp.id = :spId " +
				 "ORDER BY event.startDate";
				Query query = session.createQuery(hql);
				query.setParameter("spId", studyPeriod.getId());
				query.setDate("from", fromDate);
				query.setDate("to", toDate);
				List list = query.list();
				events.addAll(list);
				
				//get sp.cursus.university events
				hql = "SELECT distinct(event) " +
				 "FROM StudyPeriod sp " +
				 "JOIN sp.cursus.university university JOIN university.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE sp.id = :spId " +
				 "ORDER BY event.startDate";
				query = session.createQuery(hql);
				query.setParameter("spId", studyPeriod.getId());
				query.setDate("from", fromDate);
				query.setDate("to", toDate);
				list = query.list();
				events.addAll(list);
				
				ServerUtil.sortStartDateList(events);
				
				return events;
	        }
	    };
	    return (List) getHibernateTemplate().execute(callback);
	}

	@Override
	public void updateSp(final StudyPeriod sp) throws HibernateException,SQLException, IllegalAccessException {
		getHibernateTemplate().execute(new HibernateCallback<Void>() {
	         public Void doInHibernate(Session session) throws HibernateException,SQLException {
	             session.update(sp);
	             return null;
	         }
	     });
	}
}
